package com.scbo.database;

import java.util.ArrayList;
import java.util.List;

import com.scbo.DTO.Buildorder;
import com.scbo.DTO.ItemBO;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ScboDAO {
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] itemBOColumns = { MySQLiteHelper.ITEMBO_ID,
			MySQLiteHelper.ITEMBO_IDTYPE, MySQLiteHelper.ITEMBO_TEXT,
			MySQLiteHelper.ITEMBO_TTS, MySQLiteHelper.ITEMBO_TIME,
			MySQLiteHelper.ITEMBO_URL };
	private String[] buildOrderColumns = { MySQLiteHelper.BUILDORDER_ID,
			MySQLiteHelper.BUILDORDER_NAME, MySQLiteHelper.BUILDORDER_NOTE,
			MySQLiteHelper.BUILDORDER_LINK,
			MySQLiteHelper.BUILDORDER_TYPEMATCH,
			MySQLiteHelper.BUILDORDER_Mode, MySQLiteHelper.BUILDORDER_HTML };

	public ScboDAO() {
		super();
	}

	public ScboDAO(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public int createItemBO(ItemBO item) {
		long insertId = 0;
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.ITEMBO_IDTYPE, item.getBuildOrder().getId());
		values.put(MySQLiteHelper.ITEMBO_TEXT, item.getText());
		values.put(MySQLiteHelper.ITEMBO_TTS, item.getTextToSpeech());
		values.put(MySQLiteHelper.ITEMBO_TIME, item.getTime());
		values.put(MySQLiteHelper.ITEMBO_URL, item.getSoundURL());
		insertId = database.insert(MySQLiteHelper.TABLE_ITEMBO, null, values);
		return (int) insertId;
	}

	public ItemBO createItemBOreturnObj(ItemBO item) {
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.ITEMBO_IDTYPE, item.getBuildOrder().getId());
		values.put(MySQLiteHelper.ITEMBO_TEXT, item.getText());
		values.put(MySQLiteHelper.ITEMBO_TTS, item.getTextToSpeech());
		values.put(MySQLiteHelper.ITEMBO_TIME, item.getTime());
		values.put(MySQLiteHelper.ITEMBO_URL, item.getSoundURL());
		long insertId = database.insert(MySQLiteHelper.TABLE_ITEMBO, null,
				values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_ITEMBO,
				itemBOColumns, MySQLiteHelper.ITEMBO_ID + " = " + insertId,
				null, null, null, null);
		cursor.moveToFirst();
		ItemBO newItem = cursorToItemBO(cursor);
		cursor.close();
		return newItem;
	}

	public void deleteItemBO(ItemBO itemBO) {
		long id = itemBO.getId();
		Log.d("KHANHVQ", "itemBO deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_ITEMBO, MySQLiteHelper.ITEMBO_ID
				+ " = " + id, null);
	}

	public List<ItemBO> getAllitemBO() {
		List<ItemBO> itemBO = new ArrayList<ItemBO>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_ITEMBO,
				itemBOColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ItemBO comment = cursorToItemBO(cursor);
			itemBO.add(comment);
			cursor.moveToNext();
		}
		// make sure to close the cursor
		cursor.close();
		return itemBO;
	}
	public List<ItemBO> getItemsByIdType(int idType) {
		List<ItemBO> itemBO = new ArrayList<ItemBO>();
		
		Cursor cursor = database.query(MySQLiteHelper.TABLE_ITEMBO,
				itemBOColumns,  MySQLiteHelper.ITEMBO_IDTYPE
				+ " = " + idType, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ItemBO comment = cursorToItemBO(cursor);
			itemBO.add(comment);
			cursor.moveToNext();
		}
		// make sure to close the cursor
		cursor.close();
		return itemBO;
	}

	public int createBuildOrder(Buildorder item) {
		long insertId = 0;
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.BUILDORDER_NAME, item.getName());
		values.put(MySQLiteHelper.BUILDORDER_NOTE, item.getNote());
		values.put(MySQLiteHelper.BUILDORDER_LINK, item.getLink());
		values.put(MySQLiteHelper.BUILDORDER_TYPEMATCH, item.getTypeMatch());
		values.put(MySQLiteHelper.BUILDORDER_Mode, item.getTypeMode());
		values.put(MySQLiteHelper.BUILDORDER_HTML, item.getHtml());
		insertId = database.insert(MySQLiteHelper.TABLE_BUILDORDER, null,
				values);
		return (int) insertId;
	}

	public Buildorder createBuildOrderReturnObj(Buildorder item) {
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.BUILDORDER_NAME, item.getName());
		values.put(MySQLiteHelper.BUILDORDER_NOTE, item.getNote());
		values.put(MySQLiteHelper.BUILDORDER_LINK, item.getLink());
		values.put(MySQLiteHelper.BUILDORDER_TYPEMATCH, item.getTypeMatch());
		values.put(MySQLiteHelper.BUILDORDER_Mode, item.getTypeMode());
		values.put(MySQLiteHelper.BUILDORDER_HTML, item.getHtml());
		long insertId = database.insert(MySQLiteHelper.TABLE_BUILDORDER, null,
				values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_BUILDORDER,
				buildOrderColumns, MySQLiteHelper.BUILDORDER_ID + " = "
						+ insertId, null, null, null, null);
		cursor.moveToFirst();
		Buildorder newItem = cursorToBuildOrder(cursor);
		cursor.close();
		return newItem;
	}

	public void deleteBuildOrder(Buildorder BO) {
		long id = BO.getId();
		Log.d("KHANHVQ", "BO deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_BUILDORDER,
				MySQLiteHelper.BUILDORDER_ID + " = " + id, null);
	}

	public List<Buildorder> getAllBuildOrderBO() {
		List<Buildorder> buildOrderBO = new ArrayList<Buildorder>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_BUILDORDER,
				buildOrderColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Buildorder comment = cursorToBuildOrder(cursor);
			buildOrderBO.add(comment);
			cursor.moveToNext();
		}
		// make sure to close the cursor
		cursor.close();
		return buildOrderBO;
	}

	public List<Buildorder> getBoByTypeMatchTypMode(int typeMatch,
			int typeMode) {
		List<Buildorder> buildOrderBO = new ArrayList<Buildorder>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_BUILDORDER,
				buildOrderColumns, MySQLiteHelper.BUILDORDER_TYPEMATCH + " = "
						+ typeMatch + " and " + MySQLiteHelper.BUILDORDER_Mode
						+ " = " + typeMode, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Buildorder comment = cursorToBuildOrder(cursor);
			buildOrderBO.add(comment);
			cursor.moveToNext();
		}
		// make sure to close the cursor
		cursor.close();
		return buildOrderBO;
	}

	private ItemBO cursorToItemBO(Cursor cursor) {
		ItemBO newItem = new ItemBO();
		newItem.setId((int) cursor.getLong(0));
		newItem.setBuildOrder(new Buildorder((int) cursor.getLong(1), null,
				null, null, null, 0, 0));
		newItem.setText(cursor.getString(2));
		newItem.setTextToSpeech(cursor.getString(3));
		newItem.setTime(cursor.getLong(4));
		newItem.setSoundURL(cursor.getString(5));
		return newItem;
	}

	private Buildorder cursorToBuildOrder(Cursor cursor) {
		Buildorder newItem = new Buildorder();
		newItem.setId((int) cursor.getLong(0));
		newItem.setName(cursor.getString(1));
		newItem.setNote(cursor.getString(2));
		newItem.setLink(cursor.getString(3));
		newItem.setTypeMatch((int) cursor.getLong(4));
		newItem.setTypeMode((int) cursor.getLong(5));
		newItem.setHtml(cursor.getString(6));
		return newItem;
	}

}
